package nc.bs.gl.report.impl;

import java.math.BigDecimal;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import com.ufida.report.anareport.base.BaseSearchTemplateCondition;

import nc.bs.dao.BaseDAO;
import nc.bs.dao.DAOException;
import nc.bs.gl.report.itf.CustomerBalanceAuxiliaryDetailsReportService;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.pub.smart.context.SmartContext;
import nc.pub.smart.data.DataSet;
import nc.pub.smart.metadata.Field;
import nc.pub.smart.metadata.MetaData;
import nc.vo.am.common.util.CollectionUtils;
import nc.vo.pub.BusinessException;
import nc.vo.pub.lang.UFDate;
import nc.vo.pub.lang.UFLiteralDate;
import nc.vo.pubapp.AppContext;
import nccloud.dto.baseapp.querytree.dataformat.Condition;
import nccloud.dto.baseapp.querytree.dataformat.QueryTreeFormatVO;
import uap.lfw.bq.portal.base.StringUtils;

public class CustomerBalanceAuxiliaryDetailsReportServiceImpl implements CustomerBalanceAuxiliaryDetailsReportService {
	
	String[] fields = new String[] { "nov", "periodv","yearv" ,"dispname",
			                         "oppositesubj", "debitamount", "creditamount","user_name",
			                         "name","pk_custom","balance","direction","day","explanation"};
	String[] fieldCaptions = new String[] { "凭证号", "月份","年度", "科目名称",
			                                "对方科目", "借方本币", "贷方本币", "制单人", 
			                                "客户","客户编码","余额","方向","日","摘要"};
	public static String convertToInClause(String value) {  
		if(StringUtils.isBlank(value)) {
			return ""; 
		}
		String[] values = value.split(",");
          
        StringBuilder sb = new StringBuilder();  
        for (int i = 0; i < values.length; i++) {  
            sb.append('\'').append(values[i]).append('\'');   
            if (i < values.length - 1) {  
                sb.append(',');  
            }  
        }
        return sb.toString();  
	}
	
	/**
	 * 
	 * 查询预算编制明细数据
	 * 
	 * @param pk_org        财务组织
	 * @param nyear         预算年度
	 * @param pk_budgetdept 科室
	 * @return
	 * @throws BusinessException
	 */
	public List<Map<String, Object>> queryData(String yearv, String periodv)
			throws BusinessException {
		//查询本年
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct CASE gl_detail.direction   \r\n" + 
				"        WHEN 'D' THEN '借'   \r\n" + 
				"        WHEN 'C' THEN '贷'   \r\n" + 
				"        ELSE gl_detail.direction END AS direction,'记-0' || gl_detail.nov as nov ,gl_detail.explanation,gl_detail.pk_accountingbook, gl_detail.yearv, gl_detail.periodv, \n");
		sql.append("bd_accasoa.dispname, gl_detail.assid, gl_detail.pk_detail, gl_detail.price,  gl_detail.oppositesubj, gl_detail.debitamount, gl_detail.creditamount, \n");
		sql.append("gl_detail.checkstyle, gl_detail.checkno, gl_detail.checkdate, gl_detail.verifydate, gl_detail.pk_org, sm_user.user_name,bd_customer.name,gl_docfree1.f13 pk_custom, \n");
		sql.append("TO_CHAR(TO_DATE(gl_detail.prepareddatev, 'YYYY-MM-DD HH24:MI:SS'), 'DD') day, \n" );
		sql.append(" gl_detail.debitamount-gl_detail.creditamount as balance \n");
		sql.append("from gl_voucher gl_voucher \n");
		sql.append("left join  gl_detail gl_detail on  gl_voucher.pk_voucher = gl_detail.pk_voucher \n");
		sql.append("left join  gl_docfree1  gl_docfree1 on gl_detail.assid = gl_docfree1.assid  \n");
		sql.append("left join sm_user sm_user on gl_voucher.billmaker=sm_user.cuserid \n");
		sql.append("left join bd_customer  bd_customer on gl_docfree1.F13 =bd_customer.pk_customer \n");
		sql.append("left join bd_accasoa bd_accasoa on gl_detail.pk_accasoa=bd_accasoa.pk_accasoa \n");
		sql.append("where gl_detail.pk_managerv <> 'N/A'  \n");
		sql.append("and gl_detail.discardflagv <> 'Y'  \n");
		sql.append("and gl_detail.dr = 0   \n");
		sql.append("and gl_detail.voucherkindv <> 255 \n");
		sql.append("and gl_detail.voucherkindv <> 4  \n");
		sql.append("and gl_detail.voucherkindv <> 5 \n");
		sql.append("and gl_detail.pk_accasoa in ( '1001A210000000000V4D', '1001A210000000000V2M', '1001A210000000000V3G', '1001A210000000000V4G', '1001A210000000001B12', '1001A210000000001B17', '1001A210000000001E5T', '1001A210000000001B0Y', '1001A210000000001B14', '1001A210000000001B1B', '1001A210000000001B1D', '1001A210000000001B1H', '1001A210000000001B1J', '1001A210000000001E5W', '1001A210000000001E5Z', '1001A210000000001E62', '1001A21000000001L89D' ) ");
		sql.append(" and gl_docfree1.f13 <> 'NN/A'  and gl_docfree1.f13 is not null \n"); 
		sql.append("and bd_customer.name is not null");
		//sql.append("and");

        if (StringUtils.isNotBlank(yearv)) {
			sql.append("   AND gl_detail.yearv = '" + yearv + "' \n");// 年度
		}
		
		sql.append(" order by gl_detail.yearv,gl_detail.periodv,day");
//		if (StringUtils.isNotBlank(periodv)) {
//			sql.append("   AND gl_detail.adjustperiod = '"+periodv+"' \n");// 月份
//		}
//		if (StringUtils.isNotBlank(customercode)) {
//			String pk_customerString = convertToInClause(customercode);
//			sql.append("   AND gl_docfree1.F13 in (" + pk_customerString + ") \n");// 客户主键
//		}
		
		

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		//获取期初余额并计算
		//翻译对方科目
		calculateBalance(sqlResult);
		translateSubjects(sqlResult);
		
		
		
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}

	@Override
	public DataSet CustomerBalanceAuxiliaryDetailsDataProcess(SmartContext context) throws BusinessException {
		// 初始化元数据
		MetaData metadata = this.initMetaData();

		// 获取查询条件
		handelContext(context);

		BaseSearchTemplateCondition templateCondition = (BaseSearchTemplateCondition)context.getAttribute("FreeReport_All_Condition");
	//	BaseQueryCondition  templateCondition1 = (BaseQueryCondition)context.getAttribute("freereport_querycondition_innode");
		
	//	IAreaCondition ca = templateCondition1.getAreaConditions("A2:J2", null);
	//	Descriptor[] test = ca.getAllDescriptors();
	//	String sql = test.toString();
		
	
		String yearv = ""; // 年度主键
		if (null != context.getAttribute("yearv")) {
			yearv = (String) context.getAttribute("yearv");
			
		}
		//String sql2=Arrays.toString(test);

		String periodv = ""; // 月份主键
		if (null != context.getAttribute("periodv")) {
			periodv = (String) context.getAttribute("periodv");

		}
//		String customercode = ""; // 客户编码做成参照，实际查主键
//		if (null != context.getAttribute("customercode")) {
//			customercode = (String) context.getAttribute("customercode");
//		}
//		String customername = ""; // 客户名称
//		if (null != context.getAttribute("customername")) {
//			customername = (String) context.getAttribute("customername");
//		}
//		String kmcode = ""; // 会计科目 做成参照，实际查询主键
//		if (null != context.getAttribute("kmcode")) {
//			kmcode = (String) context.getAttribute("kmcode");
//		}
		
		// 查询客户余额相关数据
		List<Map<String, Object>> customerData = queryData(yearv, periodv);
		if (CollectionUtils.isEmpty(customerData))
			return new DataSet(metadata, null);
		
		// 处理数据 
		Object[][] result = dataFilter(customerData);

		return new DataSet(metadata, result);
	}
	
	/**
	 * 	初始化metadata
	 * @return
	 */
	private MetaData initMetaData() {
		Field[] FieldInfo = initField();
		MetaData md = new MetaData();
		for (int i = 0; i < fields.length; i++) {
			md.addField(FieldInfo[i]);
		}
		return md;
	}

	/**
	 * 	初始化返回字段
	 * @return
	 */
	private Field[] initField() {
		Field[] FieldInfo = new Field[fields.length];
		for (int i = 0; i < fields.length; i++) {
			Field filed = new Field();
			filed.setCaption(fieldCaptions[i]);
			filed.setDbColumnType(Types.VARCHAR);
			if (i == 5 || i == 6 ||i==10) { // 5,7对应编制金额，批复金额，金额是否需要调整字段类型，根据情况定
				filed.setDbColumnType(Types.DOUBLE);
			}
			//filed.setDbColumnType(Types.VARCHAR);
			filed.setFldname(fields[i]);
			if (i == 3 || i == 4 ||i==13 ) {// 视情况而定
				filed.setPrecision(500);
			}
			FieldInfo[i] = filed;
		}
		return FieldInfo;
	}
	
	/**
	 * 	获取当前年
	 * @return
	 */
	public String getCurrentYear() {
		UFDate serverUFDate = AppContext.getInstance().getServerTime().getDate();
		UFLiteralDate serverDate = new UFLiteralDate(serverUFDate.toDate());
		String currentYear = serverDate.getYear() + "";
		return currentYear;
	}
	
	public void handelContext(SmartContext context) {
        BaseSearchTemplateCondition templateCondition = (BaseSearchTemplateCondition)context.getAttribute("FreeReport_All_Condition");
        if(null != templateCondition) {
            QueryTreeFormatVO conditionTree = templateCondition.getConditionTree();
            List<Condition> list = conditionTree.getQuerycondition().getConditions();
            for(Condition cond : list) {
                String file = cond.getField();
                String val = cond.getValue().getFirstvalue();
                if("between".equals(cond.getOprtype())) {
                	val = cond.getValue().getFirstvalue()+","+cond.getValue().getSecondvalue();
                }
                context.setAttribute(file, val);
            }
        }
    }
	
	/**
	 * 	数据转换
	 * @param values
	 * @return
	 */
	
	
	/**
	 * 	根据主键查询年度
	 * @param nyearpk  年度
	 * @return
	 * @throws BusinessException
	 */
	public String getYear(String nyearpk) throws BusinessException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT periodyear FROM  bd_accperiod  WHERE 1 = 1 \n");
		if (StringUtils.isNotBlank(nyearpk)) {
			sql.append("   AND pk_accperiod = '" + nyearpk + "' \n");// 年度主键
		}
	
		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return (String) sqlResult.get(0).get("periodyear");
	}
	
	/**
	 * 	根据主键查询月份
	 * @param nmonthpk         月份
	 * @return
	 * @throws BusinessException
	 */
	public String getMonth(String nmonthpk) throws BusinessException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT accperiodmth FROM  bd_accperiodmonth  WHERE 1 = 1 \n");
		if (StringUtils.isNotBlank(nmonthpk)) {
			sql.append("   AND pk_accperiodmonth = '" + nmonthpk + "' \n");// 年度主键
		}
	
		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return (String) sqlResult.get(0).get("accperiodmth");
	}
	
	/**
	 * 	过滤数据
	 * @param datas
	 * @return
	 */
	private Object[][] dataFilter(List<Map<String, Object>> datas) {
		Collection<Object[]> result = new ArrayList<Object[]>();
		
		for (int i = 0; i < datas.size(); i++) {
//			datas.get(i).put("nyearpk", nyearpk);
//			datas.get(i).put("nmonthpk", nmonthpk);
			Object[] rowdata = setDatas(datas.get(i));
			result.add(rowdata);
		}
		
		return result.toArray(new Object[result.size() == 0 ? 1 : result.size()][]);
	}

	/**
	 * 组装返回数据
	 * 
	 * @param data
	 * @return
	 */
	
	
	private Object[] setDatas(Map<String, Object> data) {
		Object[] rowdata = new Object[fields.length]; 
		rowdata[0] = data.get("nov");
		rowdata[1] = data.get("periodv");
		rowdata[2] = data.get("yearv");
		rowdata[3] = data.get("dispname");
		rowdata[4] = data.get("oppositesubj");
		rowdata[5] = data.get("debitamount");
		rowdata[6] = data.get("creditamount");
		rowdata[7] = data.get("user_name");
		rowdata[8] = data.get("name");
		rowdata[9] = data.get("pk_custom");
		rowdata[10] = data.get("balance");
		rowdata[11] = data.get("direction");
		rowdata[12] = data.get("day");
		rowdata[13] = data.get("explanation");
		return rowdata;
	}
	
	private BaseDAO baseDAO;

	public BaseDAO getBaseDAO() {
		return baseDAO == null ? new BaseDAO() : baseDAO;
	}
	public void translateSubjects(List<Map<String, Object>> sqlResult) throws DAOException {
		
		for(Map<String, Object> map:sqlResult) {
			if(null!=map.get("oppositesubj")) {
			String 	originalString = map.get("oppositesubj").toString();
				 String[] parts = originalString.split(",");  
		          
			        // 创建一个StringBuilder来构建最终的字符串  
			        StringBuilder result = new StringBuilder();  
			          
			        // 遍历分割后的数组  
			        for (int i = 0; i < parts.length; i++) {  
			            // 为每个元素添加单引号  
			            result.append("'").append(parts[i]).append("'");  
			              
			            // 如果不是最后一个元素，则添加逗号  
			            if (i < parts.length - 1) {  
			                result.append(",");  
			            }  
			        }  
				String sql = "select name from bd_accasoa where pk_accasoa in ("+result+")";
				List<String> Result = (List<String>) this.getBaseDAO().executeQuery(sql,
						new ColumnListProcessor());
				StringBuilder Subjects = new StringBuilder();
				for (int i = 0; i < Result.size(); i++) {  
		            // 为每个元素添加单引号  
					Subjects.append(Result.get(i));  
		              
		            // 如果不是最后一个元素，则添加逗号  
		            if (i < Result.size() - 1) {  
		            	Subjects.append("\\");  
		            }  
		        } 
				map.put("oppositesubj", Subjects.toString());
				
				
			}
			
		}
	
		
	}
	public void calculateBalance(List<Map<String, Object>> sqlResult) throws DAOException {
		if (sqlResult == null || sqlResult.isEmpty()) {  
	        return; // 如果列表为空，则直接返回  
	    }  
		// 分组操作  
		  Map<String, List<Map<String, Object>>> grouped = sqlResult.stream()
	                .collect(Collectors.groupingBy(
	                        map -> map.get("name")+(String)map.get("yearv"), // 分组键
	                        Collectors.toList() // 收集器，这里简单地将元素放入列表中
	                ));
		  
		
		

		
		//进行分组计算 
	  for(Object key : grouped.keySet()) {
		  
		  List<Map<String,Object>> groupedList =grouped.get(key);
	    BigDecimal initialBalance = BigDecimal.ZERO; // 假设初始余额为0  
	    BigDecimal currentBalance = initialBalance; // 当前余额，从初始余额开始  
	  
	    for (int i = 0; i < groupedList.size(); i++) {  
	        Map<String, Object> record = groupedList.get(i);  
	  
	        // 假设creditamount和debitamount在Map中都是以BigDecimal类型存储的，如果不是，则需要相应地转换  
	        BigDecimal creditAmount = (BigDecimal) record.getOrDefault("creditamount", BigDecimal.ZERO);  
	        BigDecimal debitAmount = (BigDecimal) record.getOrDefault("debitamount", BigDecimal.ZERO);  
	  
	        // 计算当前记录的余额  
	        currentBalance = currentBalance.add(creditAmount).subtract(debitAmount);  
	  
	        // 将计算得到的余额添加到当前记录中  
	        record.put("balance", currentBalance);  
	  
	    }
	    } 
}
}
